﻿CREATE FUNCTION [dbo].[oitf_Security_Windows_AccessList]
(
	@WindowsUserSID NVARCHAR(100),
	@WindowsGroupSIDList NVARCHAR(MAX)
)
RETURNS @returntable TABLE 
(
	SecurityAccessID INT NOT NULL,
	SecurityAccessTitle NVARCHAR(100) NOT NULL,
	HiearchySpecific BIT NOT NULL,
	HiearchyID INT NULL,
	IncludeChildren BIT NULL
)
AS
BEGIN
	DECLARE @WinGroups TABLE(RowID INT NOT NULL, GroupSID NVARCHAR(256) NOT NULL)

	INSERT INTO @WinGroups
	SELECT * 
	FROM dbo.tf_system_SplitString(@WindowsGroupSIDList, N',', 0)

	INSERT INTO @returntable 
	SELECT DISTINCT A.*
	FROM (
	SELECT DISTINCT SAC_SecurityAccessID, SAC_AccessTitle, 
		SAC_HiearchySpecific, SGX_HierarchyID, SGX_IncludeChildren 
	FROM dbo.SecurityAccess 
	INNER JOIN dbo.SecuirtyRoleAccess 
	 ON SRA_SecurityAccessID = SAC_SecurityAccessID 
	INNER JOIN dbo.SecurityWindowsGroup_Role_Xref 
	 ON SGX_SecurityRoleID = SRA_SecurityRoleID 
	INNER JOIN dbo.SecurityWindowsGroup 
	 ON SWG_SecurityWindowsGroupID = SGX_WindowsGroupID 
	INNER JOIN @WinGroups 
	 ON GroupSID = SWG_WindowsGroupSID 
	UNION
	SELECT DISTINCT SAC_SecurityAccessID, SAC_AccessTitle,
		SAC_HiearchySpecific, SUX_HierarchyID, SUX_IncludeChildren 
	FROM dbo.SecurityAccess 
	INNER JOIN dbo.SecuirtyRoleAccess 
	 ON SRA_SecurityAccessID = SAC_SecurityAccessID 
	INNER JOIN dbo.SecurityWindowsUser_Role_Xref 
	 ON SUX_SecurityRoleID = SRA_SecurityRoleID 
	INNER JOIN dbo.SecurityWindowsUser
	 ON SUX_WindowsUserID = SWU_SecurityWindowsUserID 
	WHERE SWU_WindowsUserSID = @WindowsUserSID 
	) A

	RETURN
END